﻿using System;
using System.Data;
using System.Data.SQLite;
using System.Collections.Generic;

namespace OrionBanque.Classe.SQLite
{
    public class Chequier
    {
        static public void Delete(int id)
        {
            Classe.Log.Logger.Debug("Debut Chequier.Delete(" + id + ")");
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_DELETE_ID, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_DELETE_ID);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", id);
                Classe.Log.Logger.Debug("id=" + id);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }

        static public void Delete(Classe.Chequier u)
        {
            Chequier.Delete(u.Id);
        }

        static public Classe.Chequier Charge(int id)
        {
            Classe.Log.Logger.Debug("Debut Chequier.Charge(" + id + ")");
            Classe.Chequier u = new Classe.Chequier();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_ID, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_ID);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", id);
                Classe.Log.Logger.Debug("id=" + id);
                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    u.Id = rdr.GetInt32(0);
                    u.NumChequier = rdr.GetInt32(1);
                    u.NumCheque = rdr.GetInt32(2);
                    u.IdOperation = rdr.GetInt32(3);
                    u.IdCompte = rdr.GetInt32(4);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return u;
        }

        static public Classe.Chequier ChargePourOperation(int idOperation)
        {
            Classe.Log.Logger.Debug("Debut Chequier.ChargePourOperation(" + idOperation + ")");
            Classe.Chequier u = new Classe.Chequier();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_OPERATION, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_OPERATION);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idoperation", idOperation);
                Classe.Log.Logger.Debug("idoperation=" + idOperation);
                SQLiteDataReader rdr = cmd.ExecuteReader();
                if (rdr.Read())
                {
                    u.Id = rdr.GetInt32(0);
                    u.NumChequier = rdr.GetInt32(1);
                    u.NumCheque = rdr.GetInt32(2);
                    u.IdOperation = rdr.GetInt32(3);
                    u.IdCompte = rdr.GetInt32(4);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return u;
        }

        static public void DesaffecteCheque(int idOp)
        {
            Classe.Log.Logger.Debug("Debut Chequier.DesaffecteCheque(" + idOp+ ")");
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_DESAFFECTE, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_DESAFFECTE);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idoperation", idOp);
                Classe.Log.Logger.Debug("id_operation=" + idOp);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }

        static public List<Classe.Chequier> ChargeTout(int idCompte)
        {
            Classe.Log.Logger.Debug("Debut Chequier.ChargeTout(" + idCompte + ")");
            List<Classe.Chequier> lc = new List<Classe.Chequier>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_CREATION_OPERATION, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_CREATION_OPERATION);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idcompte", idCompte);
                Classe.Log.Logger.Debug("id_compte=" + idCompte);
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Classe.Chequier c = new Classe.Chequier();
                    c.Id = rdr.GetInt32(0);
                    c.NumChequier = rdr.GetInt32(1);
                    c.NumCheque = rdr.GetInt32(2);
                    c.IdOperation = rdr.GetInt32(3);
                    c.IdCompte = rdr.GetInt32(4);
                    lc.Add(c);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return lc;
        }

        static public List<Classe.Chequier> ChargeToutCompte(int idCompte)
        {
            Classe.Log.Logger.Debug("Debut Chequier.ChargeToutCompte(" + idCompte + ")");
            List<Classe.Chequier> lc = new List<Classe.Chequier>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_TOUT_COMPTE, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_TOUT_COMPTE);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idcompte", idCompte);
                Classe.Log.Logger.Debug("id_compte=" + idCompte);
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Classe.Chequier c = new Classe.Chequier();
                    c.Id = rdr.GetInt32(0);
                    c.NumChequier = rdr.GetInt32(1);
                    c.NumCheque = rdr.GetInt32(2);
                    c.IdOperation = rdr.GetInt32(3);
                    c.IdCompte = rdr.GetInt32(4);
                    lc.Add(c);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return lc;
        }

        static public List<Classe.Chequier> ChargeListChequeMajOpe(int idCompte, int idOperation)
        {
            Classe.Log.Logger.Debug("Debut Chequier.ChargeListChequeMajOpe(" + idCompte + ")");
            List<Classe.Chequier> lc = new List<Classe.Chequier>();
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(Classe.SQLite.Sql.CHEQUIERS_LISTE_POUR_OPERATION, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_LISTE_POUR_OPERATION);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@idcompte", idCompte);
                Classe.Log.Logger.Debug("id_compte=" + idCompte);
                cmd.Parameters.AddWithValue("@idoperation", idOperation);
                Classe.Log.Logger.Debug("id_operation=" + idOperation);
                SQLiteDataReader rdr = cmd.ExecuteReader();
                while (rdr.Read())
                {
                    Classe.Chequier c = new Classe.Chequier();
                    c.Id = rdr.GetInt32(0);
                    c.NumChequier = rdr.GetInt32(1);
                    c.NumCheque = rdr.GetInt32(2);
                    c.IdOperation = rdr.GetInt32(3);
                    c.IdCompte = rdr.GetInt32(4);
                    lc.Add(c);
                }
                rdr.Close();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }

            return lc;
        }

        static public void Maj(Classe.Chequier u)
        {
            Classe.Log.Logger.Debug("Debut Chequier.Maj(" + u.Id + ")");
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.CHEQUIERS_UPDATE_ID, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_UPDATE_ID);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@id", u.Id);
                Classe.Log.Logger.Debug("id=" + u.Id);
                cmd.Parameters.AddWithValue("@numchequier", u.NumChequier);
                Classe.Log.Logger.Debug("numchequier=" + u.NumChequier);
                cmd.Parameters.AddWithValue("@numcheque", u.NumCheque);
                Classe.Log.Logger.Debug("numcheque=" + u.NumCheque);
                cmd.Parameters.AddWithValue("@idoperation", u.IdOperation);
                Classe.Log.Logger.Debug("idoperation=" + u.IdOperation);
                cmd.Parameters.AddWithValue("@idcompte", u.IdCompte);
                Classe.Log.Logger.Debug("idcompte=" + u.IdCompte);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }

        static public void Sauve(Classe.Chequier u)
        {
            Classe.Log.Logger.Debug("Debut Chequier.Sauve(" + u.Id + ")");
            try
            {
                SQLiteCommand cmd = new SQLiteCommand(SQLite.Sql.CHEQUIERS_INSERT, SQLite.Sql.GetConnection());
                Classe.Log.Logger.Debug("Requete :" + SQLite.Sql.CHEQUIERS_INSERT);
                cmd.Prepare();
                cmd.Parameters.AddWithValue("@numchequier", u.NumChequier);
                Classe.Log.Logger.Debug("numchequier=" + u.NumChequier);
                cmd.Parameters.AddWithValue("@numcheque", u.NumCheque);
                Classe.Log.Logger.Debug("numcheque=" + u.NumCheque);
                cmd.Parameters.AddWithValue("@idoperation", u.IdOperation);
                Classe.Log.Logger.Debug("idoperation=" + u.IdOperation);
                cmd.Parameters.AddWithValue("@idcompte", u.IdCompte);
                Classe.Log.Logger.Debug("idcompte=" + u.IdCompte);
                cmd.ExecuteNonQuery();
            }
            catch (SQLiteException ex)
            {
                Classe.Log.Logger.Error(ex.Message);
                throw new Exception(String.Format(Classe.SQLite.Messages.SQLite_ERROR_GENERAL, ex.ErrorCode, ex.Message));
            }
        }
    }
}
